package impl

import (
	"adam2/internal/model"
	"adam2/internal/properties"
	"adam2/internal/util"
	"fmt"
)

type Real9QuantStockTransactionRecordDaoImpl struct {
	*BaseDaoImpl
}

// 返回dao实现类
func GetReal9QuantStockTransactionRecordDaoImpl() *Real9QuantStockTransactionRecordDaoImpl {
	return &Real9QuantStockTransactionRecordDaoImpl{GetBaseDaoImpl()}
}

// 返回表名
func (r *Real9QuantStockTransactionRecordDaoImpl) FindTableName() string {
	return "REAL9_Q_STOCK_TRANSACT_RECORD"
}

// 根据账户名，查询正在持仓的股票
func (r *Real9QuantStockTransactionRecordDaoImpl) FindByAccountNameAndSellNull(accountName string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select * from real9_q_stock_transact_record t "+
		"where t.account_name = ? and t.sell_date is null and t.sell_price is null "+
		"and t.sell_amount is null",
		accountName).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 根据账户名和日期，查询当天卖出的股票
func (r *Real9QuantStockTransactionRecordDaoImpl) FindByAccountNameAndDateAndSellNotNull(accountName string, date string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select * from real9_q_stock_transact_record qstr "+
		"where qstr.account_name = ? and qstr.sell_date=to_date(?, 'yyyy-mm-dd') "+
		"and qstr.sell_date is not null and qstr.sell_price is not null and qstr.sell_amount is not null",
		accountName, date).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 查询某个量化账户之前卖出失败的股票
func (r *Real9QuantStockTransactionRecordDaoImpl) FindQuantResolveFailSellByAccountName(accountName string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select * from real9_q_stock_transact_record t "+
		"where t.account_name = ? and t.SELL_DATE is null "+
		"and t.SELL_PRICE is null and t.SELL_AMOUNT is null and t.fail_sell_reason in(1, 2)",
		accountName).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 更新（卖出时）
func (r *Real9QuantStockTransactionRecordDaoImpl) UpdateWhenSell(everyDate string, stockTransactionDataAll model.StockTransactionDataAll, real9QuantStockTransactionRecord model.Real9QuantStockTransactionRecord) {
	// 更新real9_q_stock_transact_record表的sell_date、sell_price、sell_amount和fail_sell_reason字段
	r.db.Exec("update real9_q_stock_transact_record t "+
		"set t.sell_date = to_date(?, 'yyyy-mm-dd'), t.sell_price  = ?, t.sell_amount = t.buy_amount, "+
		"t.stamp_duty = ROUND(t.buy_amount * "+fmt.Sprintf("%f", stockTransactionDataAll.OpenPrice)+" * "+fmt.Sprintf("%f", properties.Real9Properties_.StampDutyRate)+", 4), "+
		"t.registration_fee_when_sell = ?, "+
		"t.commission_when_sell = ? "+
		"where t.id_ = ?",
		everyDate, stockTransactionDataAll.OpenPrice,
		util.CalculateRegistrationFee(real9QuantStockTransactionRecord.StockCode, real9QuantStockTransactionRecord.BuyAmount),
		util.CalculateCommission(stockTransactionDataAll.OpenPrice, real9QuantStockTransactionRecord.BuyAmount),
		real9QuantStockTransactionRecord.ID)

	// 更新real9_q_stock_transact_record表的profit_and_loss和profit_and_loss_rate字段
	r.db.Exec("update real9_q_stock_transact_record t "+
		"set t.profit_and_loss = ROUND((t.sell_price * t.sell_amount) - (t.buy_price * t.buy_amount), 4), "+
		"t.profit_and_loss_rate = (t.sell_price - t.buy_price) / t.buy_price * 100 "+
		"where t.id_ = ?", real9QuantStockTransactionRecord.ID)
}

// 查询某个账号是否有退市的股票
func (r *Real9QuantStockTransactionRecordDaoImpl) FindDelistByLastTransactionDateAndAccountName(lastTransactionDate string, accountName string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select qstr.* from real9_q_stock_transact_record qstr "+
		"join stock_info si_ on si_.code_=qstr.stock_code and si_.last_transaction_date is not null and si_.last_transaction_date=to_date(?, 'yyyy-mm-dd') "+
		"where qstr.account_name=? and qstr.sell_date is null and qstr.sell_price is null and qstr.sell_amount is null",
		lastTransactionDate, accountName).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 查询某个量化账户的买入的股票的交易记录，不包括real9_q_stock_filter表中已经存在的记录
func (r *Real9QuantStockTransactionRecordDaoImpl) FindByAccountNameAndNotInReal9QuantStockFilter(accountName string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select * from real9_q_stock_transact_record t "+
		"where t.account_name = ? and t.SELL_DATE is null "+
		"and t.SELL_PRICE is null and t.SELL_AMOUNT is null and t.stock_code not in("+
		"select t1.stock_code from real9_q_stock_filter t1)", accountName).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 通过id，更新fail_sell_reason字段
func (r *Real9QuantStockTransactionRecordDaoImpl) UpdateFailSellReasonById(id int, failSellReason int) {
	r.db.Exec("update real9_q_stock_transact_record t set t.fail_sell_reason=? where t.id_=?", failSellReason, id)
}

// 更新real9_q_account表
func (r *Real9QuantStockTransactionRecordDaoImpl) UpdateAfterBuy(real9QuantAccount model.Real9QuantAccount,
	everyDate string, sellOrBuyStockNumber int, stockAssets float64, currentBuyOrSellStockAssets float64) {
	var registrationFeeWhenBuy float64
	r.db.Raw("select COALESCE(sum(t1.registration_fee_when_buy), 0) from real9_q_stock_transact_record t1 "+
		"where t1.account_name = ? and t1.sell_price is null and t1.sell_amount is null and t1.sell_date is null "+
		"and t1.buy_date=to_date(?,'yyyy-mm-dd')",
		real9QuantAccount.AccountName, everyDate).Scan(&registrationFeeWhenBuy)
	var commissionWhenBuy float64
	r.db.Raw("select COALESCE(sum(t1.commission_when_buy), 0) from real9_q_stock_transact_record t1 "+
		"where t1.account_name = ? and t1.sell_price is null and t1.sell_amount is null and t1.sell_date is null "+
		"and t1.buy_date=to_date(?,'yyyy-mm-dd')",
		real9QuantAccount.AccountName, everyDate).Scan(&commissionWhenBuy)
	var totalStampDuty float64
	r.db.Raw("select COALESCE(sum(t1.stamp_duty), 0) from real9_q_stock_transact_record t1 where t1.account_name = ?",
		real9QuantAccount.AccountName).Scan(&totalStampDuty)
	var totalRegistrationFeeWhenBuy float64
	r.db.Raw("select COALESCE(sum(t1.registration_fee_when_buy), 0) from real9_q_stock_transact_record t1 where t1.account_name = ?",
		real9QuantAccount.AccountName).Scan(&totalRegistrationFeeWhenBuy)
	var totalCommissionWhenBuy float64
	r.db.Raw("select COALESCE(sum(t1.commission_when_buy), 0) from real9_q_stock_transact_record t1 where t1.account_name = ?",
		real9QuantAccount.AccountName).Scan(&totalCommissionWhenBuy)
	r.db.Exec("update real9_q_account t "+
		"set t.hold_stock_number = ?, t.stock_assets = round(?, 4), "+
		"t.total_assets = round(? + t.capital_assets - ? - ? - ?, 4), "+
		//"t.total_assets = round(t.total_assets - ? - ?, 4), "+
		// 下面这行可能有错，正确的可能是：资金资产=总资产-股票资产，否则资金资产部分可能就会出现负数。
		//"t.capital_assets = round(t.total_assets - ? - ? - ?, 4), "+
		"t.capital_assets = round(t.capital_assets - ? - ? - ?, 4), "+
		//"t.total_stamp_duty = ?,"+
		"t.total_registrate_fee_when_buy = ?,"+
		"t.total_commission_when_buy = ? "+
		//"t.total_registrate_fee_when_sell = ?, "+
		//"t.total_commission_when_sell = ? "+
		"where t.account_name = ?",
		sellOrBuyStockNumber, stockAssets,
		stockAssets, currentBuyOrSellStockAssets, registrationFeeWhenBuy, commissionWhenBuy,
		//stockAssets, registrationFeeWhenBuy, commissionWhenBuy,
		/*stockAssets, registrationFeeWhenBuy, commissionWhenBuy,*/
		currentBuyOrSellStockAssets, registrationFeeWhenBuy, commissionWhenBuy,
		//sumStampDuty,
		totalRegistrationFeeWhenBuy,
		totalCommissionWhenBuy,
		//sumRegistrationFeeWhenSell,
		//sumCommissionWhenSell,
		real9QuantAccount.AccountName)

	// 向表quant_account_log中插入数据
	//u.db.Exec("insert into quant_account_log(DATE_, ACCOUNT_NAME, HOLD_STOCK_NUMBER, STOCK_ASSETS, "+
	//	"CAPITAL_ASSETS, TOTAL_ASSETS, TOTAL_STAMP_DUTY, TOTAL_REGISTRATE_FEE_WHEN_BUY, TOTAL_COMMISSION_WHEN_BUY, "+
	//	"TOTAL_REGISTRATE_FEE_WHEN_SELL, TOTAL_COMMISSION_WHEN_SELL) "+
	//	"select to_date(?, 'yyyy-mm-dd'), t.ACCOUNT_NAME, t.HOLD_STOCK_NUMBER, t.STOCK_ASSETS, t.CAPITAL_ASSETS, t.TOTAL_ASSETS, t.TOTAL_STAMP_DUTY, "+
	//	"t.TOTAL_REGISTRATE_FEE_WHEN_BUY, t.TOTAL_COMMISSION_WHEN_BUY, t.TOTAL_REGISTRATE_FEE_WHEN_SELL, t.TOTAL_COMMISSION_WHEN_SELL "+
	//	"from quant_account t where t.account_name = ?",
	//	transactionDate, (*quantAccount).AccountName)
}

// 根据账户名称，查询在real9_q_stock_transact_record表中，但不在real9_o_stock_transact_record表中未卖出的股票记录，不包括当天是最后一个交易日的股票
func (r *Real9QuantStockTransactionRecordDaoImpl) FindByAccountNameAndSellNullAndNotInReal9OptimizeStockTransactionRecord(accountName string, date string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select t.* from real9_q_stock_transact_record t "+
		"join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(?, 'yyyy-mm-dd') "+
		"where t.account_name=? and t.sell_date is null and t.sell_price is null and t.sell_amount is null "+
		"and t.stock_code not in(select ostr.stock_code from real9_o_stock_transact_record ostr "+
		"where ostr.sell_date is null and ostr.sell_price is null and ostr.sell_amount is null) "+
		"and t.stock_code not in("+
		"select si_.code_ from stock_info si_ where si_.last_transaction_date=to_date(?,'yyyy-mm-dd')) "+
		"order by stda.total_market_value asc",
		date, accountName, date).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 根据卖出日期查询记录
func (r *Real9QuantStockTransactionRecordDaoImpl) FindBySellDate(sellDate string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select * from real9_q_stock_transact_record qstr "+
		"where qstr.sell_date=to_date(?, 'yyyy-mm-dd')", sellDate).Scan(&real9QuantStockTransactionRecordArray)
	return real9QuantStockTransactionRecordArray
}

// 根据buy_date，删除sell_date为空的记录
func (r *Real9QuantStockTransactionRecordDaoImpl) DeleteBySellDateNullAndBuyDate(date string) {
	r.db.Exec("delete real9_q_stock_transact_record where buy_date=to_date(?,'yyyy-mm-dd') and sell_date is null", date)
}

// 根据sell_date，将sell_date、sell_price、sell_amount、profit_and_loss、profit_and_loss_rate、stamp_duty、registration_fee_when_sell、commission_when_sell字段改为null
func (r *Real9QuantStockTransactionRecordDaoImpl) UpdateNullBySellDate(date string) {
	r.db.Exec("update real9_q_stock_transact_record "+
		"set sell_date=null, sell_price=null, sell_amount=null, profit_and_loss=null, profit_and_loss_rate=null, stamp_duty=null, registration_fee_when_sell=null, commission_when_sell=null "+
		"where sell_date=to_date(?,'yyyy-mm-dd')", date)
}

// 根据accountName和stockCode，查询sell_date为空的那一条记录
func (r *Real9QuantStockTransactionRecordDaoImpl) FindByAccountNameAndStockCodeAndSellDateNull(accountName string, stockCode string) model.Real9QuantStockTransactionRecord {
	var real9QuantStockTransactionRecord model.Real9QuantStockTransactionRecord
	r.db.Raw("select t.* from real9_q_stock_transact_record t "+
		"where t.account_name=? and t.stock_code=? and t.sell_date is null and t.sell_price is null and t.sell_amount is null",
		accountName, stockCode).Scan(&real9QuantStockTransactionRecord)
	return real9QuantStockTransactionRecord
}